package com.cw.jeeyt.service.component;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Collections;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.collections.CollectionUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Hyperlink;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import com.cw.jeeyt.common.enums.EnumDocColorStyle;
import com.cw.jeeyt.service.intf.api.ApiDataGenenratorService;
import com.cw.jeeyt.common.utils.FileUtils;
import com.cw.jeeyt.common.utils.StringUtil;
import com.cw.jeeyt.service.bo.api.ApiDatabaseDocConfigBo;
import com.cw.jeeyt.service.bo.api.ApiTableBo;
import com.cw.jeeyt.service.bo.api.ApiTableColumnBo;
import com.cw.jeeyt.service.bo.api.ApiTableIndexBo;
import com.cw.lang.common.utils.CollectionUtil;

/**
 * 数据库表导出excel业务实现类
 */
@Component
public class ApiGenerateDatabaseExcelComponent {
    private static Logger logger = LoggerFactory.getLogger(ApiGenerateDatabaseExcelComponent.class);
    private static Integer currentRow = 0;
    private static XSSFWorkbook workbook;
    private static CellStyle menuStyle;
    private static CellStyle cellStyle;
    private static CellStyle linkStyle;
    @Value("${database.export.outputPath}")
    private String databaseExportOutputPath;
    @Autowired
    private ApiDataGenenratorService apiDataGenenratorService;

    /**
     * 获取并拼装数据库信息
     */
    private void exportExcel(List<ApiTableBo> apiTableList) throws Exception {
        //创建目录
        createCatalogSheet(apiTableList);
        //创建数据表
        createTableSheet(apiTableList);
    }

    private void createTableSheet(List<ApiTableBo> apiTableList) {
        if (CollectionUtils.isEmpty(apiTableList)) {
            return;
        }
        //排序
        Collections.sort(apiTableList, (o1, o2) -> {
            int res = o1.getTableNum()
                .compareTo(o2.getTableNum());
            return res;
        });
        int ii = 1;
        for (ApiTableBo apiTableExcel : apiTableList) {
            logger.info("开始生成{}【{}】excel信息", apiTableExcel.getTableCode(), apiTableExcel.getTableDesc());
            currentRow = 0;
            String sheetName = apiTableExcel.getTableCode();
            if (sheetName.length() > 31) {
                sheetName = sheetName.substring(0, 31);
            }
            Sheet sheet = workbook.createSheet(sheetName);
            sheet.setDisplayGridlines(false);
            //表信息
            createTableRow(apiTableExcel, sheet);
            //表字段
            createTableColumnRow(apiTableExcel, sheet);
            //表索引
            createTableIndexRow(apiTableExcel, sheet);
            //自适应宽度
            for (int i = 0; i < 9; i++) {
                sheet.autoSizeColumn(i);
                sheet.setColumnWidth(i, 5000);
            }
            ii++;
        }
    }

    /**
     * 创建表索引信息
     *
     * @param apiTableExcel
     * @param sheet
     */
    private void createTableIndexRow(ApiTableBo apiTableExcel, Sheet sheet) {
        //菜单
        Row indexHeadRow = sheet.createRow(currentRow);
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 1));
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 2, 3));
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 4, 5));
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 6, 8));
        Cell cell = indexHeadRow.createCell(0);
        cell.setCellStyle(menuStyle);
        cell.setCellValue("索引编码");
        Cell cell1 = indexHeadRow.createCell(1);
        cell1.setCellStyle(menuStyle);
        cell1.setCellValue("");

        Cell cell2 = indexHeadRow.createCell(2);
        cell2.setCellStyle(menuStyle);
        cell2.setCellValue("唯一");

        Cell cell3 = indexHeadRow.createCell(3);
        cell3.setCellStyle(menuStyle);
        cell3.setCellValue("");

        Cell cell4 = indexHeadRow.createCell(4);
        cell4.setCellStyle(menuStyle);
        cell4.setCellValue("类型");

        Cell cell5 = indexHeadRow.createCell(5);
        cell5.setCellStyle(menuStyle);
        cell5.setCellValue("");

        Cell cell6 = indexHeadRow.createCell(6);
        cell6.setCellStyle(menuStyle);
        cell6.setCellValue("索引字段");

        Cell cell7 = indexHeadRow.createCell(7);
        cell7.setCellStyle(menuStyle);
        cell7.setCellValue("");

        Cell cell8 = indexHeadRow.createCell(8);
        cell8.setCellStyle(menuStyle);
        cell8.setCellValue("");
        Map<String, List<ApiTableIndexBo>> indexMap = apiTableExcel.getIndexMap();
        if (CollectionUtil.isEmpty(indexMap)) {
            return;
        }
        currentRow += 1;
        Iterator iterator = indexMap.keySet()
            .iterator();
        while (iterator.hasNext()) {
            String key = (String) iterator.next();
            List<ApiTableIndexBo> indexBos = indexMap.get(key);
            for (ApiTableIndexBo index : indexBos) {
                Row indexRow = sheet.createRow(currentRow);
                sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 0, 1));
                sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 2, 3));
                sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 4, 5));
                sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 6, 8));
                Cell indexCell = indexRow.createCell(0);
                indexCell.setCellValue(index.getIndexCode());
                indexCell.setCellStyle(cellStyle);
                Cell indexCell1 = indexRow.createCell(1);
                indexCell1.setCellValue("");
                indexCell1.setCellStyle(cellStyle);
                Cell indexCell2 = indexRow.createCell(2);
                indexCell2.setCellValue(index.getIndexUnique());
                indexCell2.setCellStyle(cellStyle);
                Cell indexCell3 = indexRow.createCell(3);
                indexCell3.setCellValue("");
                indexCell3.setCellStyle(cellStyle);
                Cell indexCell4 = indexRow.createCell(4);
                indexCell4.setCellValue(index.getIndexType());
                indexCell4.setCellStyle(cellStyle);
                Cell indexCell5 = indexRow.createCell(5);
                indexCell5.setCellValue("");
                indexCell5.setCellStyle(cellStyle);
                Cell indexCell6 = indexRow.createCell(6);
                indexCell6.setCellValue(index.getIndexColumns());
                indexCell6.setCellStyle(cellStyle);
                Cell indexCell7 = indexRow.createCell(7);
                indexCell7.setCellValue("");
                indexCell7.setCellStyle(cellStyle);
                Cell indexCell8 = indexRow.createCell(8);
                indexCell8.setCellValue("");
                indexCell8.setCellStyle(cellStyle);
                currentRow += 1;
            }
        }
    }

    /**
     * 创建表字段信息
     *
     * @param apiTableExcel
     * @param sheet
     */
    private void createTableColumnRow(ApiTableBo apiTableExcel, Sheet sheet) {
        //菜单
        String[] menus = new String[] {"字段编码", "字段类型", "长度", "空值", "自增", "默认值", "字段说明", "字段注释"};
        Row columnHeadRow = sheet.createRow(currentRow);
        for (int i = 0; i < menus.length; i++) {
            Cell cell = columnHeadRow.createCell(i);
            cell.setCellStyle(menuStyle);
            cell.setCellValue(menus[i]);
        }
        sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 7, 8));
        Cell cell8 = columnHeadRow.createCell(8);
        cell8.setCellStyle(menuStyle);
        cell8.setCellValue("");
        List<ApiTableColumnBo> columnList = apiTableExcel.getColumnList();
        if (columnList == null || columnList.isEmpty()) {
            return;
        }
        currentRow += 1;
        for (ApiTableColumnBo column : columnList) {
            Row columnRow = sheet.createRow(currentRow);
            for (int i = 0; i < menus.length; i++) {
                Cell cell = columnRow.createCell(i);
                cell.setCellStyle(cellStyle);
                switch (i) {
                    case 0:
                        cell.setCellValue(column.getColumnCode());
                        break;
                    case 1:
                        cell.setCellValue(column.getColumnType());
                        break;
                    case 2:
                        cell.setCellValue(column.getColumnLength());
                        break;
                    case 3:
                        cell.setCellValue(column.getColumnIsNull());
                        break;
                    case 4:
                        boolean autoIncrement = column.isAutoIncrement();
                        String auto = "否";
                        if (autoIncrement) {
                            auto = "是";
                        }
                        cell.setCellValue(auto);
                        break;
                    case 5:
                        cell.setCellValue(column.getDefaultVal());
                        break;
                    case 6:
                        cell.setCellValue(column.getColumnDesc());
                        break;
                    case 7:
                        cell.setCellValue(column.getColumnComment());
                        break;
                }
            }
            sheet.addMergedRegion(new CellRangeAddress(currentRow, currentRow, 7, 8));
            Cell cell_8 = columnRow.createCell(8);
            cell_8.setCellStyle(cellStyle);
            cell_8.setCellValue("");
            currentRow += 1;
        }
    }

    /**
     * 表信息行
     *
     * @param apiTableExcel
     * @param sheet
     */
    private void createTableRow(ApiTableBo apiTableExcel, Sheet sheet) {
        //表信息行
        Row tableRow = sheet.createRow(currentRow);
        short a = 500;
        tableRow.setHeight(a);
        //序号
        Cell tableNumCell = tableRow.createCell(0);
        tableNumCell.setCellValue("序号");
        tableNumCell.setCellStyle(menuStyle);
        Cell tableNumValueCell = tableRow.createCell(1);
        tableNumValueCell.setCellValue(apiTableExcel.getTableNum());
        tableNumValueCell.setCellStyle(cellStyle);
        //表名
        Cell tableCodeCell = tableRow.createCell(2);
        tableCodeCell.setCellValue("表名");
        tableCodeCell.setCellStyle(menuStyle);
        Cell tableCodeValueCell = tableRow.createCell(3);
        tableCodeValueCell.setCellValue(apiTableExcel.getTableCode());
        tableCodeValueCell.setCellStyle(cellStyle);
        Cell tableDatabaseCell = tableRow.createCell(4);
        tableDatabaseCell.setCellValue("所在数据库");
        tableDatabaseCell.setCellStyle(menuStyle);
        Cell tableDatabaseValueCell = tableRow.createCell(5);
        tableDatabaseValueCell.setCellValue(apiTableExcel.getDatabaseCode());
        tableDatabaseValueCell.setCellStyle(cellStyle);
        //说明
        Cell tableDescCell = tableRow.createCell(6);
        tableDescCell.setCellValue("说明");
        tableDescCell.setCellStyle(menuStyle);
        Cell tableDescValueCell = tableRow.createCell(7);
        tableDescValueCell.setCellStyle(cellStyle);
        tableDescValueCell.setCellValue(apiTableExcel.getTableDesc());
        //返回
        Cell cell = tableRow.createCell(8);
        Hyperlink hyperlink = workbook.getCreationHelper()
            .createHyperlink(HyperlinkType.DOCUMENT);
        hyperlink.setAddress("#目录!B" + (apiTableExcel.getTableNum() + 1));
        cell.setCellValue("返回目录");
        cell.setCellStyle(linkStyle);
        cell.setHyperlink(hyperlink);
        currentRow += 1;
    }

    /**
     * 创建目录
     */
    private void createCatalogSheet(List<ApiTableBo> apiTableList) throws Exception {
        if (CollectionUtils.isEmpty(apiTableList)) {
            throw new Exception("数据为空");
        }
        Collections.sort(apiTableList, (o1, o2) -> {
            int res = o1.getTableNum()
                .compareTo(o2.getTableNum());
            return res;
        });

        Sheet catalogSheet = workbook.createSheet("目录");
        catalogSheet.setDisplayGridlines(false);
        Row catalogRow = catalogSheet.createRow(0);
        String[] menus = new String[] {"序号", "数据表名", "数据表说明", "所在数据库"};
        for (int i = 0; i < menus.length; i++) {
            Cell cell = catalogRow.createCell(i);
            cell.setCellStyle(menuStyle);
            cell.setCellValue(menus[i]);
        }
        //创建目录列表
        if (apiTableList == null || apiTableList.isEmpty()) {
            return;
        }
        int currentRow = 1;
        for (ApiTableBo apiTableExcel : apiTableList) {
            Row tableRow = catalogSheet.createRow(currentRow);
            //序号
            Cell tableNumCell = tableRow.createCell(0);
            //表编码
            Cell tableCodeCell = tableRow.createCell(1);
            //表说明
            Cell tableDescCell = tableRow.createCell(2);
            //数据库编码
            Cell databaseCodeCell = tableRow.createCell(3);

            tableNumCell.setCellValue(apiTableExcel.getTableNum());
            tableCodeCell.setCellValue(apiTableExcel.getTableCode());
            tableDescCell.setCellValue(apiTableExcel.getTableDesc());
            databaseCodeCell.setCellValue(apiTableExcel.getDatabaseCode());
            tableNumCell.setCellStyle(cellStyle);
            tableCodeCell.setCellStyle(linkStyle);
            tableDescCell.setCellStyle(cellStyle);
            databaseCodeCell.setCellStyle(cellStyle);
            Hyperlink hyperlink = workbook.getCreationHelper()
                .createHyperlink(HyperlinkType.DOCUMENT);
            String sheetName = apiTableExcel.getTableCode();
            if (sheetName.length() > 31) {
                sheetName = sheetName.substring(0, 31);
            }
            hyperlink.setAddress("#" + sheetName + "!C1");
            tableCodeCell.setHyperlink(hyperlink);
            currentRow = currentRow + 1;
        }
        //自适应宽度
        for (int j = 0; j < menus.length; j++) {
            catalogSheet.autoSizeColumn(j, true);
        }
    }

    /**
     * 生成数据库文档信息
     */
    public void generateDatabaseDoc(Long databaseId, ApiDatabaseDocConfigBo apiDatabaseDocConfigBo,
        HttpServletRequest request, HttpServletResponse response) throws Exception {
        FileOutputStream xlsStream = null;
        try {
            workbook = new XSSFWorkbook();
            menuStyle = formatMenu(apiDatabaseDocConfigBo.getGenerateDocColorStyle());
            cellStyle = formatCell();
            linkStyle = formatLinkCell();
            //1、获取并拼装数据库信息
            List<ApiTableBo> apiTableExcels = initDatabaseInfo(databaseId, apiDatabaseDocConfigBo);
            //2、生成excel
            exportExcel(apiTableExcels);
            File xlsFile = new File(databaseExportOutputPath + "/" + databaseId + ".xlsx");
            if (!xlsFile.exists()) {
                //xlsFile.createNewFile();
                FileUtils.createFile(databaseExportOutputPath + "/" + databaseId + ".xlsx");
            }
            //输出文件
            xlsStream = new FileOutputStream(xlsFile);
            workbook.write(xlsStream);
            logger.info("数据库文档生成成功，路径{}", databaseExportOutputPath);
            FileUtils.downFile(xlsFile, request, response);
        } catch (Exception e) {
            throw new Exception(e.getMessage());
        } finally {
            if (xlsStream != null) {
                try {
                    xlsStream.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 初始化数据库表信息
     */
    private List<ApiTableBo> initDatabaseInfo(Long databaseId, ApiDatabaseDocConfigBo apiDatabaseDocConfigBo)
    throws Exception {
        return apiDataGenenratorService.generateData(databaseId, apiDatabaseDocConfigBo.getGenerateTablePrefix());
    }

    /**
     * 格式化菜单
     *
     * @return HSSFCellStyle
     */
    private CellStyle formatMenu(String docColorStyle) {
        //设置字体
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 14);
        font.setBold(true);
        //粗体
        //font.setBold(true);

        CellStyle style = workbook.createCellStyle();
        style.setFont(font);
        //居中
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setFillBackgroundColor(IndexedColors.SKY_BLUE.index);
        style.setFillForegroundColor(IndexedColors.SKY_BLUE.index);
        if (StringUtil.isNotEmpty(docColorStyle)) {
            if (EnumDocColorStyle.find(docColorStyle) != null) {
                style.setFillBackgroundColor(EnumDocColorStyle.find(docColorStyle)
                    .getDescription());
                style.setFillForegroundColor(EnumDocColorStyle.find(docColorStyle)
                    .getDescription());
            }
        }
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        //设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);

        return style;
    }

    /**
     * 格式化单元格
     *
     * @return HSSFCellStyle
     */
    private CellStyle formatCell() {

        //设置字体
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 10);
        font.setBold(false);

        CellStyle style = workbook.createCellStyle();

        style.setFont(font);

        //居中
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        //设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    /**
     * 链接
     *
     * @return
     */
    private CellStyle formatLinkCell() {

        //设置字体
        Font font = workbook.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 10);
        font.setBold(false);
        font.setColor(IndexedColors.BLUE.index);
        font.setUnderline((byte) 1);

        CellStyle style = workbook.createCellStyle();

        style.setFont(font);

        //居中
        style.setAlignment(HorizontalAlignment.LEFT);
        style.setVerticalAlignment(VerticalAlignment.CENTER);

        //设置边框
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }
}
